﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DTO;

namespace DAO
{
    public class NhanVienDAO
    {
        public static DataTable LayDanhSachNhanVien()
        {
            string sql;
            sql = "SELECT * FROM [NhanVien]";
            return DataAccess.ExecQuery(sql);
        }

        public static DataTable LayDanhSachNhanVienByUserName(string username)
        {
            string sql;
            sql = "SELECT UserName FROM [NhanVien] where UserName = '" + username + "'";
            return DataAccess.ExecQuery(sql);
        }

        public static DataTable Login(string username, string password)
        {
            string sql;
            sql = string.Format("SELECT MaNhanVien, TenNhanVien, UserName, BoPhan FROM [NhanVien] WHERE UserName = '{0}' AND Password = '{1}'", username, password);
            return DataAccess.ExecQuery(sql);
        }

        public static void ThemNhanVien(NhanVienDTO obj)
        {
            string sql = "";
            sql = string.Format(@"INSERT INTO [NhanVien](TenNhanVien, UserName, Password, NgaySinh, DiaChi, SoDienThoai, BoPhan) VALUES(N'{0}', N'{1}', N'{2}', N'{3}', N'{4}', N'{5}', N'{6}')",
                                                obj.TenNhanVien, obj.UserName, obj.Password, obj.NgaySinh, obj.DiaChi, obj.SoDienThoai, obj.BoPhan);
            DataAccess.ExecNonQuery(sql);
        }

        public static void SuaNhanVien(NhanVienDTO obj)
        {
            string sql = "";
            sql = string.Format(@"UPDATE [NhanVien]
                                SET TenNhanVien = N'{0}', UserName = N'{1}', Password = N'{2}', NgaySinh = N'{3}', DiaChi = N'{4}', SoDienThoai = N'{5}', BoPhan= N'{6}'
                                WHERE MaNhanVien = {7}", obj.TenNhanVien, obj.UserName, obj.Password, obj.NgaySinh, obj.DiaChi, obj.SoDienThoai, obj.BoPhan, obj.MaNhanVien);
            DataAccess.ExecNonQuery(sql);
        }

        public static void XoaNhanVien(int maNhanVien)
        {
            string sql;
            sql = string.Format("DELETE [NhanVien] WHERE MaNhanVien = {0}", maNhanVien);
            DataAccess.ExecNonQuery(sql);
        }

        public static NhanVienDTO LayThongTinNhanVienTheoMaNhanVien(int maNhanVien)
        {
            NhanVienDTO kq = new NhanVienDTO();
            string sql;
            sql = @"select * FROM [NhanVien]
                    where MaNhanVien = " + maNhanVien;
            DataTable dt = DataAccess.ExecQuery(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                kq.MaNhanVien = Convert.ToInt32(dt.Rows[0]["MaNhanVien"]);
                kq.TenNhanVien = Convert.ToString(dt.Rows[0]["TenNhanVien"]);
                kq.UserName = Convert.ToString(dt.Rows[0]["UserName"]);
                kq.Password = Convert.ToString(dt.Rows[0]["Password"]);
                kq.NgaySinh = Convert.ToDateTime(dt.Rows[0]["NgaySinh"]);
                kq.DiaChi = Convert.ToString(dt.Rows[0]["DiaChi"]);
                kq.SoDienThoai = Convert.ToString(dt.Rows[0]["SoDienThoai"]);
                kq.BoPhan = Convert.ToString(dt.Rows[0]["BoPhan"]);                
            }
            return kq;
        }
    }
}
